package com.tdam_2012_g7.data;

import entidades.Accion;
import entidades.Contacto;
import entidades.Mensaje;
import entidades.Usuario;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;

public class DataBaseHelper extends SQLiteOpenHelper{
	
	static final String DATABASE_NAME = "contacts.db";
	static final int DATABASE_VERSION = 1;
	
	static final String usersTable="Usuarios";
	static final String colUsrID="UsuarioID";
	static final String colContID="ContactoID";
	static final String colName="UsuarioName";
	static final String colPass="UsuarioPass";
	static final String colPhone="Phone";
	
	static final String actionsTable="Acciones";
	static final String colActID="AccionID";
	static final String colActName="AccionName";
	static final String colActDesc="AccionDescrip";
	static final String colActFrom="AccionIDFrom";
	static final String colActNameTo="AccionNameTo";
	static final String colActTo="AccionIdTo";
	static final String colActTipo="AccionTipo";
	static final String colActDate="AccionDate";
	
	static final String messagesTable="Mensajes";
	static final String colMsgID="MensajeID";
	static final String colMsg="Mensaje";
	static final String colAction="Accion";
	
	static final String connectivityTable="Conectividad";
	static final String colConID="ConectividadID";
	static final String colConDesc="Conectividad";
	static final String colConDate="ConectividadDate";
	
	
	public DataBaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	


	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("CREATE TABLE "+usersTable+" ("+colUsrID+ " INTEGER PRIMARY KEY , "+
				colContID+ " TEXT, "+ 
				colName+ " TEXT, "+ 
				colPass + " TEXT, " +
				colPhone+ " TEXT)");
		
		db.execSQL("CREATE TABLE "+actionsTable+" ("+colActID+ " INTEGER PRIMARY KEY , "+
				colActName+ " TEXT, "+
				colActDesc+ " TEXT, "+
				colActTo + " TEXT, " +
				colActNameTo + " TEXT, " +
				colActTipo + " TEXT, " +
				colActDate+ " TEXT, "+colActFrom+" INTEGER NOT NULL ,FOREIGN KEY ("+colActFrom+") REFERENCES "+usersTable+" ("+colUsrID+"));");
		
		db.execSQL("CREATE TABLE "+messagesTable+" ("+colMsgID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
				colMsg+" TEXT, "+colAction+" INTEGER NOT NULL ,FOREIGN KEY ("+colAction+") REFERENCES "+actionsTable+" ("+colActID+"));");
		
		db.execSQL("CREATE TABLE "+connectivityTable+" ("+colConID+ " INTEGER PRIMARY KEY , "+
				colConDesc+ " TEXT, "+
				colConDate+ " TEXT)");
		
		
		db.execSQL("CREATE TRIGGER fk_actUsr_usrid " +
				" BEFORE INSERT "+
				" ON "+actionsTable+
				
				" FOR EACH ROW BEGIN"+
				" SELECT CASE WHEN ((SELECT "+colUsrID+" FROM "+usersTable+" WHERE "+colUsrID+"=new."+colActFrom+" ) IS NULL)"+
				" THEN RAISE (ABORT,'Foreign Key Violation') END;"+
				"  END;");
		
		db.execSQL("CREATE TRIGGER fk_msjact_actid " +
				" BEFORE INSERT "+
				" ON "+messagesTable+
				
				" FOR EACH ROW BEGIN"+
				" SELECT CASE WHEN ((SELECT "+colActID+" FROM "+actionsTable+" WHERE "+colActID+"=new."+colAction+" ) IS NULL)"+
				" THEN RAISE (ABORT,'Foreign Key Violation') END;"+
				"  END;");
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS "+usersTable);
		db.execSQL("DROP TABLE IF EXISTS "+messagesTable);
		db.execSQL("DROP TABLE IF EXISTS "+actionsTable);
	}
	
	public void AddUser(Usuario usr)
	{
		 
		 
		SQLiteDatabase db= this.getWritableDatabase();
		 
		
		ContentValues cv=new ContentValues();
		
		cv.put(colName, usr.getNombreUsuario());
		cv.put(colPass, usr.getPassword());
		cv.put(colPhone, "");
		
		db.insert(usersTable, null, cv);
		
		db.close();
		
		
	}
	
	public void AddWebMsg(Long actId, Mensaje msj)
	{
		 
		 
		SQLiteDatabase db= this.getWritableDatabase();
		 
		
		ContentValues cv=new ContentValues();
		
		cv.put(colMsg, msj.getMensaje());
		cv.put(colAction, String.valueOf(actId));
		
		db.insert(messagesTable, null, cv);
		
		db.close();
		
		
	}
	
	public long AddAccion(Usuario usr, Accion act, Mensaje msj)
	{
		 
		 
		SQLiteDatabase db= this.getWritableDatabase();
		 
		
		ContentValues cv=new ContentValues();
		
		cv.put(colActName, act.getNombreAccion());
		
		cv.put(colActTo, String.valueOf(act.getIdContacto()));
		cv.put(colActNameTo, act.getNombreContacto());
		cv.put(colActDate, act.getFecha());
		cv.put(colActDesc, act.getDescripcion());
		cv.put(colActFrom, String.valueOf(1));
		cv.put(colActTipo, String.valueOf(act.getIdTipoAccion()));
		
		long id = db.insert(actionsTable, null, cv);
		
		db.close();
		
		if (msj!=null)
			AddWebMsg(id, msj);
		return id;
		
	}
	
	public long AddAccion(Contacto cont, Accion act, Mensaje msj)
	{
		 
		 
		SQLiteDatabase db= this.getWritableDatabase();
		 
		
		ContentValues cv=new ContentValues();
		
		cv.put(colActName, act.getNombreAccion());
		cv.put(colActNameTo, cont.getNombre());
		cv.put(colActTo, cont.getId());
		cv.put(colActDate, act.getFecha());
		cv.put(colActDesc, act.getDescripcion());
		cv.put(colActFrom, String.valueOf(1));
		cv.put(colActTipo, String.valueOf(act.getIdTipoAccion()));
		
		long id = db.insert(actionsTable, null, cv);
		
		db.close();
		
		return id;
		
	}
	
	public void AddConnectivity(String conn, String date)
	{
		 
		 
		SQLiteDatabase db= this.getWritableDatabase();
		 
		
		ContentValues cv=new ContentValues();
		
		cv.put(colConDesc, conn);
		cv.put(colConDate, date);
		
		db.insert(connectivityTable, null, cv);
		
		db.close();
		
		
	}
	
	public Cursor getAllMensajes(Contacto cont)
	 {
		 SQLiteDatabase db=this.getWritableDatabase();
		 
		 String id = cont.getId();
		 Cursor cur= db.rawQuery("SELECT "+colMsgID+" as _id, "+colMsg+", "+colActDate+", "+colActTo+
				 ", "+colActTipo+", "+colActName+", "+colActNameTo+
				 " FROM mensajes m inner join "+
				 "acciones a on m.Accion=a.AccionID where a.AccionIdTo=?", new String[] {id});
		 return cur;
		 
	 }
	
	public Cursor getAllEmails(Contacto cont)
	 {
		 SQLiteDatabase db=this.getWritableDatabase();
		 
		 String id = cont.getId();
		 Cursor cur= db.rawQuery("SELECT "+colActID+" as _id, "+colActDesc+", "+colActDate+", "+colActTo+
				 " FROM acciones where AccionNameTo=? and AccionName=?", new String[] {id, "Email"});
		 return cur;
		 
	 }
	
	public Cursor getAllAcciones()
	 {
		 SQLiteDatabase db=this.getWritableDatabase();
		 
		 Cursor cur= db.rawQuery("SELECT "+colActID+" as _id, "+colActName+", "+colActDate+", "+colActTo+ ", "+
				 colActDesc+ ", "+colActTipo+ ", "+colActNameTo+
				 " FROM acciones", new String[] {});
		 return cur;
		 
	 }
	
	public Cursor getUsuario(Usuario usr)
	 {
		 SQLiteDatabase db=this.getWritableDatabase();
		 
		 String name = usr.getNombreUsuario();
		 String pass = usr.getPassword();
		 Cursor cur= db.rawQuery("SELECT "+colUsrID+" as _id, "+colName+", "+colPass+
				 " FROM usuarios where UsuarioName=? and UsuarioPass=?", new String[] {name, pass});
		 return cur;
		 
	 }

}
